﻿IF EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'OTGet')
	BEGIN
		DROP  Procedure  OTGet
	END
GO



CREATE PROCEDURE OTGet
	(	
		@EmpresaId	AS INT,
		@NumeroOT	AS VARCHAR(30)
	)	
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @entregas TABLE (id INT)

	-- Lista de tablas a ser devueltas a la aplicacion para cargar la hoja de registro	
	SELECT 0 AS TableId, 'Catalog'				AS TableName	UNION
	SELECT 1 AS TableId, 'OrdenTrabajo'			AS TableName	UNION
	SELECT 2 AS TableId, 'OTEntrega'			AS TableName	UNION
	SELECT 3 AS TableId, 'OTDetalle'			AS TableName	UNION
	SELECT 4 AS TableId, 'OTMaterial'			AS TableName    UNION
	SELECT 5 AS TableId, 'OTGasto'				AS TableName   -- UNION		

	INSERT @entregas (Id)
	SELECT e.Id
	FROM Lit_OTEntrega e
	INNER JOIN Lit_OT o ON (e.OTId = o.Id)
	WHERE o.NumeroOT = @NumeroOT
	
	-- OrdenTrabajo
	SELECT	Id, EmpresaId, FechaIngreso, FechaInicial, FechaFinal, FechaEntrega,
			NumeroOT, ClienteId, C.Cliente_Nombre ClienteNombre, ArticuloId AS ProductoId, 
			Art_Nombre AS ProductoNombre, VendedorId, Cantidad, EstadoId
	FROM Lit_OT OT
	LEFT JOIN Cliente C ON OT.EmpresaId = C.Emp_id AND OT.ClienteId = C.Cliente_id
	LEFT JOIN Articulo A ON OT.EmpresaId = A.Emp_id AND OT.ArticuloId = A.Art_Id
	WHERE NumeroOT = @NumeroOT  

	-- OTEntrega
	SELECT	oe.Id, Orden, FechaInicial, FechaFinal, UnidadesSolicitadas,
			UnidadesProducidas, ManoObraIndirecta, EstadoId, e.Estado AS EstadoNombre, OTId
	FROM Lit_OTEntrega oe
	INNER JOIN Lit_Estado e ON e.Id = oe.EstadoId
	WHERE oe.Id IN (SELECT Id FROM @entregas)
		
	-- OTDetalle
	SELECT	Id, OTEntregaId, Fecha, OperarioId, AyudanteId, MaquinaId, Cantidad,
			HorasRegular, HorasExtra, HorasDoble, Tipo, Descripcion 
	FROM Lit_OTDetalle
	WHERE OTEntregaId IN (SELECT Id FROM @entregas)
	
	-- OTMaterial
	SELECT	Id, OTEntregaId, Fecha, ArticuloId AS MaterialId, Tipo, Cantidad, 
			OperarioId, Departamento
	FROM Lit_OTMaterial
	WHERE OTEntregaId IN (SELECT Id FROM @entregas)
	
	-- OTGasto
	SELECT	Id, OTEntregaId, GastoId, Monto
	FROM Lit_OTGasto
	WHERE OTEntregaId IN (SELECT Id FROM @entregas)
           	            
	SET NOCOUNT OFF
END

GO